RedshiftでJSON配列をUNNESTしてみた

はじめに

仙台オフィス所属、データアナリティクス事業本部のおざわです。

昨年12月に仙台オフィスが開設してから、徐々に仙台でイベントが走り始めています。6月14日(金)にはビール片手にわいわい交流するゆるめのイベントがあります。近くにお住まいでご興味がある方はぜひご参加いただければと思います。

また7月4日(木)にはDevelopersIO仙台、TohokuTechが開催されます！こちらもぜひご参加ください。

やりたいこと

今回やりたかったのは、Redshiftに格納されている下のようなJSON配列から値を抜き出して他のテーブルと結合するというものです。

{
  "name": "my_lovely_json",
  "target_json_array": [
    {"user_id": 1, "json_col1":"foo1", "json_col2": "bar1", "json_col3": "fuga1"},
    {"user_id": 2, "json_col1":"foo2", "json_col2": "bar2", "json_col3": "fuga2"},
    {"user_id": 3, "json_col1":"foo3", "json_col2": "bar3", "json_col3": "fuga3"},
  ]
}

target_json_arrayからjson_col1とjson_col2の値を抜き出して、別テーブルと結合してこんな形↓にしたい

user_id	username	col1	col2
1	a	foo1	bar1
2	b	foo2	bar2
3	c	foo3	bar3

方法

さっそくやり方ですが、こちらのブログ記事に書かれているとおりでした。

FROM句でUNNEST

ポイントとしては4行目の文字列にcastしてからjson_parseしている部分と16行目のFROM句にJSON配列を指定するところです。super型のカラムに格納されていても、そのままでは扱えないデータもあったので、一旦文字列にcastしてからjson_parseするのがよいかと思います。

with my_json as (
    select
        user_id,
        json_parse(my_json_col::varchar) as my_json_col
    from
        "my_schema"."my_json_table_text"
),
my_table as (
    select
        user_id,
        -- ダブルクォートが残るので置換
        replace(ary.json_col1::varchar, '"', '') as json_col1,
        replace(ary.json_col2::varchar, '"', '') as json_col2
    from
        my_json as js,
        js.my_json_col.target_json_array as ary
),
joined_users as (
    select
        t.user_id,
        u.username,
        t.json_col1 as col1,
        t.json_col2 as col2
    from
        my_table as t
        left join　"my_schema"."my_users" as u　on t.user_id = u.user_id
),
final as (
    select
        user_id,
        username,
        col1,
        col2
    from
        joined_users
)
select *
from final;

ちなみにインデックスが必要な場合は以下のように at 〜で取得できます。

my_table as (
    select
        i,
        user_id,
        -- ダブルクォートが残るので置換
        replace(ary.json_col1::varchar, '"', '') as json_col1,
        replace(ary.json_col2::varchar, '"', '') as json_col2
    from
        my_json as js,
        js.my_json_col.target_json_array as ary at i
)

この内容で記事としては終了なのですが、他の方法でも一応同じことができましたのでメモを残したいと思います。

JSON関数でがんばる

Stack Overflowや比較的古めの記事で紹介されているのは下記のような手法でした。恐らくsuper型が登場する以前に文字列として格納されたJSONを扱うために先人たちが残した方法と思います。

with recursive seq(i) as (
    select 0 as i
    union all
    select i + 1 as i from seq 
    where i < 10
),
my_json as (
    select
        user_id,
        json_extract_path_text(my_json_col, 'target_json_array') as json_array
    from
        "my_schema"."my_json_table_text"
),
my_json_with_size AS (
    select
        user_id,
        json_array,
        json_array_length(json_array) as array_count
    from
        my_json
),
-- seqと結合して項目を取得
my_table as (
    select
        seq.i,
        user_id,
        json_extract_path_text(
            json_extract_array_element_text(json_array, seq.i), 'json_col1'
        ) as json_col1,
        json_extract_path_text(
            json_extract_array_element_text(json_array, seq.i), 'json_col2'
        ) as json_col2
    from
        my_json_with_size
    cross join
        seq
    where seq.i < array_count
),
joined_users as (
    select
        t.user_id,
        u.username,
        t.json_col1 as col1,
        t.json_col2 as col2
    from
        my_table as t
    left join 
        "my_schema"."my_users" as u on t.user_id = u.user_id
),
final as (
    select
        user_id,
        username,
        col1,
        col2
    from 
        joined_users
)
select *
from final;

今回は配列の要素が一定数に収まることがわかっていたので、最初に必要なだけ連番を生成しています。json_extract_array_element_text関数でインデックスを指定してオブジェクトを抜き出してから、json_extract_path_text関数で値を取得しています。最初の方法と比較すると余計なステップがある分わかりにくくなっています。

終わりに

以上、RedshiftでJSON配列をUNNESTする方法２つでした。

参考リンク